ORACLE 7 (SQL) Program

This next page is only for programmers. It will not make sense if you are not into programming...sorry?
AUTHOR: ESTEBAN CAZAREZ
DESCRIPTION: CREATES FIVE DATABASES IN SQL LANGUAGE.
PROFR. DELUCA

 
DROP TABLE SC_RELATION;
DROP TABLE STUDENT;
DROP TABLECLASS;
DROP TABLE TEACHER;
DROP TABLE OFFICE;

 
CREATE TABLE office (
office_id number(2) not null,
office_size number(4),
window varchar2(1) constraint window_chk check
(window = 'Y' or window = 'N'),
constraint office_primary_key primary key (office_id));

 
insert into office values (01,1000,'Y');
insert into office values (02,2000,'Y');
insert into office values (03,3000,'N');
insert into office values (04,4000,'N');
insert into office values (05,5000,'N');

 
CREATE TABLE teacher(
teacher_id number(4) not null,
name varchar2(20),
address varchar2(20),
phone varchar2(9),
office_id number(2) unique,
constraint teacher_foreign_key foreign key(office_id) references office(office_id),
constraint teacher_primary_key primary key(teacher_id));

 
insert into teacher values (2201 , 'ESTEBAN CAZAREZ' , '677 W. UPS
Ave.' , '999-9999', 01 );
insert into teacher values (2202 , 'MARIA CAZAREZ' , '100 W. KOREE
Ave.' , '327-4569' , 02 );
insert into teacher values (2203 , 'ROBERT JACKSON' , '126 W. KITTY
Ave.' , '313-4569' , 03 );
insert into teacher values (2204 , 'ANITA MILLSPAUGH' , '300 W. LINGSLEY
Ave.' , '355-4569' , 04 );
insert into teacher values (2205 , 'JOHN STUBBE' , '400 W. NINGELKYU
Ave.' , '117-4569' ,05 );

 
CREATE TABLE class (
class_number number(4) not null,
description varchar2(20),
credit_hours number(1),
teacher_id number(4) not null,
constraint class_foreign_key foreign key(teacher_id) references
teacher(teacher_id),
constraint class_primary_key primary key(class_number));
insert into class values ( 5200 , 'PSYCHOLOGY' , 4 , 2201 );
insert into class values ( 5201 , 'ALGEBRA ' , 3 , 2202 );
insert into class values ( 5202 , 'COMP-373 ' , 4 , 2203 );
insert into class values ( 5203 , 'VIS. BASIC' , 4 , 2204 );
insert into class values ( 5204 , 'VISUAL C++' , 4 , 2205 );

 
CREATE TABLE student (
student_id number(4) not null,
name varchar2(20),
address varchar2(15),
phone varchar2(8),
dob varchar2(8),
gpa number(4,2),
credits number(3),
dept_code number(2),
constraint student_primary_key primary key (student_id));

 
insert into student values ( 7701 , 'MEMO PAVORAZ' , '628
N.NANOT' , '622-3456' , '11-12-70' , 3.01 , 100,60 );
insert into student values ( 7702, 'ZAMA LELELET' , '200
E.KOREE' , '333-3333' , '12-12-70' , 3.10 , 101 , 61 );
insert into student values ( 7703 , 'CINI ZAXXSON' , '626
E.LALAL' , '444-4444' , '11-11-79' , 3.20 , 102 , 62 );
insert into student values ( 7704 , 'LOLO MATTITA' , '380
W.LOLOE' , '222-4343' , '01-01-80' , 4.21 , 103 , 63 );
insert into student values ( 7705 , 'AMAR JAONINA' , '900
E.NINEY' , '323-5551' , '12-01-90' , 3.22 , 104 , 64 );

 
CREATE TABLE sc_relation (
student_id numeric(4) not null,
class_number
numeric(4) not null,
constraint sc_primary_key primary key(student_id,class_number),
constraint stud_foreign_key foreign key(student_id) references
student(student_id),
constraint clas_foreign_key foreign key(class_number) references
class(class_number));

 
insert into sc_relation values( 7701 , 5200 );
insert into sc_relation values( 7702 , 5201 );
insert into sc_relation values( 7703 , 5202 );
insert into sc_relation values( 7704 , 5203 );
insert into sc_relation values( 7705 , 5204 );

 
SPOOL a:\report.txt; SET LINESIZE 80
SET FEEDBACK OFF TTITLE 'PROJECT LAB 3|REPORT'
BREAK ON s.name SKIP 1
ON REPROT SELECT s.name "Student Name",t.name "Teacher's Name",
c.class_number "Class Number",c.description "Class Description"
FROM student s,teacher t,class c, sc_relation sc
WHERE s.student_id = sc.student_id AND c.class_number = sc.class_number
and t.teacher_id = c.teacher_id;
SPOOL OFF;
 

~Back to main~